﻿using Common;
using System;
using System.Collections.Generic;
using System.Text;
using SqliteDataAccess;
using SqliteDataAccess.SqliteDataManagerTableAdapters;
using OfficeOpenXml;
using System.IO;
using System.Diagnostics;

namespace Business
{
    public class LaporanSemesterBusiness : BaseBusiness
    {

        public override int Load()
        {
            throw new NotImplementedException();
        }

        public override int Save()
        {
            LaporanSemesterTableAdapter laporanSemesterTA;
            try
            {
                laporanSemesterTA = new LaporanSemesterTableAdapter();
                return laporanSemesterTA.Update(LppbopSqlite.LaporanSemester);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public int LoadBySemester(String semester, int tahun)
        {
            int sms = (int)Enums.GetSemesterByName(semester);
            LaporanSemesterTableAdapter laporanSemesterTA = new LaporanSemesterTableAdapter();
            return laporanSemesterTA.Fill(LppbopSqlite.LaporanSemester, sms, tahun);
        }

        public int InsertLaporanSemester(string semester, int tahun)
        {
            int sms = (int)Enums.GetSemesterByName(semester);
            BukuKasUmumTableAdapter bukuKasUmumTA = new BukuKasUmumTableAdapter();

            int[] months = null;
            if (semester.Equals(EnumSemester.I.ToString()))
            {
                months = new int[] { 1, 2, 3, 4, 5, 6 };
            }
            else if (semester.Equals(EnumSemester.II.ToString()))
            {
                months = new int[] { 7, 8, 9, 10, 11, 12 };
            }

            SqliteDataManager.BukuKasUmumDataTable bukuKasUmum_1 = bukuKasUmumTA.GetDataByBulan(months[0], months[1], months[2], tahun);
            SqliteDataManager.BukuKasUmumDataTable bukuKasUmum_2 = bukuKasUmumTA.GetDataByBulan(months[3], months[4], months[5], tahun);

            if (bukuKasUmum_1.Count == 0 && bukuKasUmum_2.Count == 0)
            {
                return 0;
            }

            long anggaran1 = 0, anggaran2 = 0, realisasi1 = 0, realisasi2 = 0;
            anggaran1 = bukuKasUmum_1.TotalPenerimaan;
            anggaran2 = bukuKasUmum_2.TotalPenerimaan;
            realisasi1 = bukuKasUmum_1.TotalPengeluaran;
            realisasi2 = bukuKasUmum_2.TotalPengeluaran;

            LaporanSemesterTableAdapter laporanSemesterTA = new LaporanSemesterTableAdapter();
            SqliteDataManager.LaporanSemesterDataTable laporanSemester = laporanSemesterTA.GetData(sms, tahun);
            if (laporanSemester.Count == 0)
            {
                SqliteDataManager.LaporanSemesterRow newRow = laporanSemester.NewLaporanSemesterRow();
                newRow.semester = sms;
                newRow.tahun = tahun;
                newRow.no = "1.";
                newRow.uraian = string.Format("Dana BOP Semester {0}", semester);
                newRow.prosentase_anggaran = (anggaran1 + anggaran2) == 0 ? 0 : (double)(realisasi1 + realisasi2) / (double)(anggaran1 + anggaran2);
                newRow.jumlah_anggaran = anggaran1 + anggaran2;
                newRow.realisasi_anggaran_1 = sms == 1 ? realisasi1 : 0;
                newRow.realisasi_anggaran_2 = sms == 1 ? realisasi2 : 0;
                newRow.realisasi_anggaran_3 = sms == 2 ? realisasi1 : 0;
                newRow.realisasi_anggaran_4 = sms == 2 ? realisasi2 : 0;
                newRow.jumlah_realisasi = realisasi1 + realisasi2;
                newRow.pajak_yang_diterima = bukuKasUmum_1.TotalPajakDiterima + bukuKasUmum_2.TotalPajakDiterima;
                newRow.pajak_yang_disetorkan = bukuKasUmum_1.TotalPajakDisetor + bukuKasUmum_2.TotalPajakDisetor;
                newRow.prosentase_pajak = newRow.pajak_yang_diterima == 0 ? 0 : (double)newRow.pajak_yang_disetorkan / (double)newRow.pajak_yang_diterima;
                newRow.sisa_dana = anggaran1 + anggaran2 - realisasi1 - realisasi2;
                newRow.keterangan = "";
                laporanSemester.AddLaporanSemesterRow(newRow);
            }
            else
            {
                SqliteDataManager.LaporanSemesterRow row = laporanSemester[0];
                row.prosentase_anggaran = (double)(realisasi1 + realisasi2) / (double)(anggaran1 + anggaran2);
                row.jumlah_anggaran = anggaran1 + anggaran2;
                row.realisasi_anggaran_1 = sms == 1 ? realisasi1 : 0;
                row.realisasi_anggaran_2 = sms == 1 ? realisasi2 : 0;
                row.realisasi_anggaran_3 = sms == 2 ? realisasi1 : 0;
                row.realisasi_anggaran_4 = sms == 2 ? realisasi2 : 0;
                row.jumlah_realisasi = realisasi1 + realisasi2;
                row.pajak_yang_diterima = bukuKasUmum_1.TotalPajakDiterima + bukuKasUmum_2.TotalPajakDiterima;
                row.pajak_yang_disetorkan = bukuKasUmum_1.TotalPajakDisetor + bukuKasUmum_2.TotalPajakDisetor;
                row.prosentase_pajak = row.pajak_yang_diterima == 0 ? 0 : (double)row.pajak_yang_disetorkan / (double)row.pajak_yang_diterima;
                row.sisa_dana = anggaran1 + anggaran2 - realisasi1 - realisasi2;
            }

            return laporanSemesterTA.Update(laporanSemester);
        }

        public void PrintExcel(SqliteDataManager.LaporanSemesterDataTable table)
        {
            string fileName = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx";
            WriteToExcel(fileName, table);

            try
            {
                ProcessStartInfo processInfo = new ProcessStartInfo();
                processInfo.FileName = fileName;
                processInfo.WindowStyle = ProcessWindowStyle.Hidden;
                processInfo.Verb = "Print";
                processInfo.CreateNoWindow = true;
                processInfo.UseShellExecute = true;
                Process process = Process.Start(processInfo);
                if (process != null)
                    process.WaitForExit();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                File.Delete(fileName);
            }
        }

        public void ExportToExcel(string filename, SqliteDataManager.LaporanSemesterDataTable table)
        {
            WriteToExcel(filename, table);
        }

        public void WriteToExcel(string filename, SqliteDataManager.LaporanSemesterDataTable table)
        {
            if (table.Count == 0) return;

            SqliteDataManager.ProfilSekolahRow profil = new ProfilBusiness().GetProfil();

            using (ExcelPackage package = new ExcelPackage(new FileInfo(String.Format(@"templates\{0}", "laporan-semester.xlsx"))))
            {
                ExcelWorksheet ws = package.Workbook.Worksheets[1];

                int row = 14;
                foreach (SqliteDataManager.LaporanSemesterRow record in table.Rows)
                {
                    ws.Cells["A" + row].Value = record.no;
                    ws.Cells["B" + row].Value = record.uraian;
                    ws.Cells["C" + row].Value = record.prosentase_anggaran;
                    ws.Cells["D" + row].Value = record.jumlah_anggaran;
                    ws.Cells["E" + row].Value = record.realisasi_anggaran_1;
                    ws.Cells["F" + row].Value = record.realisasi_anggaran_2;
                    ws.Cells["G" + row].Value = record.realisasi_anggaran_3;
                    ws.Cells["H" + row].Value = record.realisasi_anggaran_4;
                    ws.Cells["I" + row].Value = record.jumlah_realisasi;
                    ws.Cells["J" + row].Value = record.prosentase_pajak;
                    ws.Cells["K" + row].Value = record.pajak_yang_diterima;
                    ws.Cells["L" + row].Value = record.pajak_yang_disetorkan;
                    ws.Cells["M" + row].Value = record.sisa_dana;
                    ws.Cells["N" + row].Value = record.keterangan;

                    row++;
                }

                // nama sekolah
                ws.Cells["C7"].Value = string.Format(": {0}", profil.sekolah);
                // tahun anggaran
                ws.Cells["C8"].Value = string.Format(": {0}", table[0].tahun);
                // format jumlah, anggaran, realisasi
                ws.Cells["D14:I14,K14:L14"].Style.Numberformat.Format = "_(Rp* #,##0.00_);_(Rp* (#,##0.00);_(Rp* \" - \"??_);_(@_)";
                // format persentase
                ws.Cells["C14,J14"].Style.Numberformat.Format = "0.00%";
                // tempat, tanggal
                ws.Cells["L17"].Value = string.Format("Jakarta, {0}", DateTime.Now.ToString("dd MMMM yyyy"));
                // ketua yayasan
                ws.Cells["B24"].Value = profil.ketua_yayasan;
                // kepala sekolah
                ws.Cells["H24"].Value = profil.kepala_sekolah;
                // bendahara sekolah
                ws.Cells["L24"].Value = profil.bendahara_sekolah;

                if (!filename.EndsWith("xlsx")) filename += ".xlsx";
                package.SaveAs(new FileInfo(filename));
            }
        }
    }
}
